package com.Servlet;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jdbc.Jdbc;
import com.project.bean.Classes;
import com.project.bean.Sales;

/**
 * Servlet implementation class classinfoOutput
 */
@WebServlet("/classinfoOutput")
public class classinfoOutput extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public classinfoOutput() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("application/octet-stream");
		response.setCharacterEncoding("utf-8");
		// 设置文件的名字
		//response.addHeader("Content-Disposition", "attachment:filename=classinfo.xls");
		response.addHeader("Content-Disposition", "attachment; filename=classinfo.xls");
		String headline = "班级信息";
		String[] title = { "分中心名称", "班级名", "外教上课时间", "中教上课时间", "外教姓名", "中教姓名", "班级级别", "开班时间", "结束时间", "班级总人数",
				"可加入人数" };
		// 创建excel工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建一个工作的sheet
		HSSFSheet sheet = workbook.createSheet();
		CellStyle cellStyle = workbook.createCellStyle();
		DataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));// 默认为文本类型
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		// 创建标题
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
		cell = row.createCell(0);
		cell.setCellValue(headline);
		// 创建第二行
		HSSFRow rowtitle = sheet.createRow(1);
		// 插入第二行的title
		for (int i = 0; i < title.length; i++) {
			sheet.setDefaultColumnStyle(i, cellStyle);
			sheet.setColumnWidth(i, 20 * 256);// 设置好cell的宽度
			cell = rowtitle.createCell(i);
			cell.setCellValue(title[i]);
		}
		String find = "select * from class";
		Jdbc j = new Jdbc();
		ResultSet rs;
		List<Classes> list = new ArrayList<>();

		try {
			j.Jdbc_Q2(find);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		rs = j.rs;
		try {
			while (rs.next()) {
				Classes c = new Classes();
				c.setCentername(rs.getString("centername"));
				c.setClassesname(rs.getString("classname"));
				c.setFttime(rs.getString("fttime"));
				c.setCttime(rs.getString("cttime"));
				c.setFteacher(rs.getString("fteacher"));
				c.setCteacher(rs.getString("cteacher"));
				c.setClasslevel(rs.getString("classlevel"));
				c.setStartdate(rs.getString("startdate"));
				c.setEnddate(rs.getString("enddate"));
				c.setStunum(rs.getString("stunum"));
				c.setStunumrest(rs.getString("stunumrest"));
				list.add(c);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println("容量为:"+list.size());
		int i = 2;
		for (Classes c : list) {
		    System.out.println("asdasdasdasd");
			HSSFRow nextrow = sheet.createRow(i++);
			HSSFCell cell2 = nextrow.createCell(0);
			cell2.setCellValue(c.getCentername());
			cell2 = nextrow.createCell(1);
			cell2.setCellValue(c.getClassesname());
			cell2 = nextrow.createCell(2);
			cell2.setCellValue(c.getFttime());
			cell2 = nextrow.createCell(3);
			cell2.setCellValue(c.getCttime());
			cell2 = nextrow.createCell(4);
			cell2.setCellValue(c.getFteacher());
			cell2 = nextrow.createCell(5);
			cell2.setCellValue(c.getCteacher());
			cell2 = nextrow.createCell(6);
			cell2.setCellValue(c.getClasslevel());
			cell2 = nextrow.createCell(7);
			cell2.setCellValue(c.getStartdate());
			cell2 = nextrow.createCell(8);
			cell2.setCellValue(c.getEnddate());
			cell2 = nextrow.createCell(9);
			cell2.setCellValue(c.getStunum());
			cell2 = nextrow.createCell(10);
			cell2.setCellValue(c.getStunumrest());
		}
		workbook.write(response.getOutputStream());
		workbook.close();
	}

}
